import pandas as pd
import requests
from datetime import datetime
import re
from pathlib import Path
import time

def extract_repo_info(url):
    """
    从GitHub或Gitee链接中提取仓库信息
    """
    # 移除可能的查询参数和锚点，但保持URL完整
    clean_url = url.split('?')[0].split('#')[0]
    
    # 匹配GitHub链接: https://github.com/username/repo
    github_pattern = r'https://github\.com/([^/]+)/([^/]+?)(?:\.git)?/?$'
    # 匹配Gitee链接: https://gitee.com/username/repo
    gitee_pattern = r'https://gitee\.com/([^/]+)/([^/]+?)(?:\.git)?/?$'
    
    github_match = re.search(github_pattern, clean_url)
    gitee_match = re.search(gitee_pattern, clean_url)
    
    if github_match:
        return {
            'platform': 'github',
            'username': github_match.group(1),
            'repo': github_match.group(2)
        }
    elif gitee_match:
        return {
            'platform': 'gitee',
            'username': gitee_match.group(1),
            'repo': gitee_match.group(2)
        }
    else:
        return None

def get_github_commits(username, repo, token=None):
    """
    获取GitHub仓库的提交历史
    """
    url = f'https://api.github.com/repos/{username}/{repo}/commits'
    headers = {
        'Accept': 'application/vnd.github.v3+json'
    }
    
    # 如果提供了token，则添加到请求头中
    if token:
        headers['Authorization'] = f'token {token}'
    
    try:
        response = requests.get(url, headers=headers)
        if response.status_code == 403:
            print(f"GitHub API访问受限，请稍后再试或配置访问令牌")
            return []
        elif response.status_code == 404:
            print(f"GitHub仓库不存在或无访问权限: {username}/{repo}")
            return []
        elif response.status_code == 401:
            print(f"GitHub认证失败，请检查访问令牌")
            return []
        response.raise_for_status()
        commits = response.json()
        
        commit_data = []
        for commit in commits:
            commit_info = {
                'sha': commit['sha'],
                'message': commit['commit']['message'],
                'author': commit['commit']['author']['name'],
                'date': commit['commit']['author']['date']
            }
            commit_data.append(commit_info)
        
        return commit_data
    except requests.exceptions.RequestException as e:
        print(f"获取GitHub提交历史时出错: {e}")
        return []

def get_gitee_commits(username, repo, token=None):
    """
    获取Gitee仓库的提交历史
    """
    url = f'https://gitee.com/api/v5/repos/{username}/{repo}/commits'
    params = {}
    
    # 如果提供了token，则添加到参数中
    if token:
        params['access_token'] = token
    
    try:
        response = requests.get(url, params=params)
        if response.status_code == 403:
            print(f"Gitee API访问受限，请稍后再试或配置访问令牌")
            return []
        elif response.status_code == 404:
            print(f"Gitee仓库不存在或无访问权限: {username}/{repo}")
            return []
        elif response.status_code == 401:
            print(f"Gitee认证失败，请检查访问令牌")
            return []
        response.raise_for_status()
        commits = response.json()
        
        commit_data = []
        for commit in commits:
            commit_info = {
                'sha': commit['sha'],
                'message': commit['commit']['message'],
                'author': commit['commit']['author']['name'],
                'date': commit['commit']['author']['date']
            }
            commit_data.append(commit_info)
        
        return commit_data
    except requests.exceptions.RequestException as e:
        print(f"获取Gitee提交历史时出错: {e}")
        return []

def read_excel_file(file_path):
    """
    读取Excel文件，提取仓库链接和相关信息
    """
    try:
        # 使用pandas读取Excel文件
        df = pd.read_excel(file_path)
        
        repos = []
        
        # 假设D列是链接，B列是姓名，C列是学号
        for index, row in df.iterrows():
            url = str(row['gitee/github链接']).strip() if pd.notna(row['gitee/github链接']) else ''
            
            if not url:
                continue
                
            repo_info = extract_repo_info(url)
            if repo_info:
                repo_info['name'] = str(row['姓名']) if pd.notna(row['姓名']) else ''
                repo_info['student_id'] = str(row['学号']) if pd.notna(row['学号']) else ''
                repo_info['class'] = str(row['班级']) if pd.notna(row['班级']) else ''
                repo_info['original_url'] = url  # 保存原始链接
                
                repos.append(repo_info)
            else:
                print(f"无法解析链接: {url}")
        
        return repos
    except Exception as e:
        print(f"读取Excel文件时出错: {e}")
        return []

def parse_date(date_str):
    """
    解析不同格式的日期字符串
    """
    try:
        # 尝试解析带时区的日期
        if date_str.endswith('Z'):
            # GitHub格式: 2023-01-01T10:00:00Z
            return datetime.strptime(date_str, "%Y-%m-%dT%H:%M:%SZ")
        elif '+' in date_str or date_str.count('-') > 2:
            # Gitee格式: 2023-01-01T10:00:00+08:00
            # 或者: 2023-01-01T10:00:00-05:00
            return datetime.fromisoformat(date_str)
        else:
            # 其他格式
            return datetime.fromisoformat(date_str)
    except Exception as e:
        print(f"日期解析错误: {date_str}, 错误: {e}")
        return None

def write_commit_report(commits_data, output_file):
    """
    将提交信息写入报告文件
    """
    with open(output_file, 'w', encoding='utf-8') as f:
        f.write("# 仓库提交情况统计报告\n\n")
        
        # 总体统计信息
        total_commits = 0
        student_stats = {}
        
        for repo_data in commits_data:
            f.write(f"## 学生: {repo_data['name']} (学号: {repo_data['student_id']})\n")
            f.write(f"班级: {repo_data['class']}\n")
            f.write(f"仓库链接: {repo_data['original_url']}\n\n")  # 使用原始链接
            
            if not repo_data['commits']:
                f.write("未找到提交记录（仓库可能存在但无提交，或无访问权限）\n\n")
                # 即使没有提交记录，也要在统计中包含该学生
                student_stats[repo_data['name']] = 0
                continue
                
            f.write("| 提交SHA | 作者 | 提交时间 | 提交信息 |\n")
            f.write("|---------|------|----------|----------|\n")
            
            for commit in repo_data['commits']:
                # 统计总提交次数
                total_commits += 1
                
                # 格式化日期
                date_obj = parse_date(commit['date'])
                if date_obj:
                    formatted_date = date_obj.strftime("%Y-%m-%d %H:%M:%S")
                else:
                    formatted_date = "日期解析失败"
                
                # 截取提交信息的前50个字符
                short_message = commit['message'][:50] + "..." if len(commit['message']) > 50 else commit['message']
                
                f.write(f"| {commit['sha'][:8]} | {commit['author']} | {formatted_date} | {short_message} |\n")
            
            f.write("\n")
            
            # 单个仓库统计信息
            f.write("### 统计信息\n")
            f.write(f"该仓库总提交次数: {len(repo_data['commits'])}\n\n")
            
            # 按学生统计（每个学生对应其仓库的提交次数）
            student_stats[repo_data['name']] = len(repo_data['commits'])
            
            f.write("提交作者列表:\n")
            authors = set()
            for commit in repo_data['commits']:
                authors.add(commit['author'])
            
            for author in authors:
                f.write(f"- {author}\n")
            f.write("\n")
        
        # 最终总体统计
        f.write("# 总体统计信息\n\n")
        f.write(f"所有仓库总提交次数: {total_commits}\n\n")
        
        f.write("按学生统计提交次数:\n")
        for student, count in sorted(student_stats.items(), key=lambda x: x[1], reverse=True):
            f.write(f"- {student}: {count} 次提交\n")
        f.write("\n")

def main():
    # 配置部分
    excel_file = 'E:\python test\pachong\students.xlsx'  # Excel文件路径
    output_file = 'commit_report.md'  # 输出报告文件
    github_token = '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'  # 可选：GitHub访问令牌（请替换为你的实际令牌）
    gitee_token = 'b4409362c92f71477ee3d866048741da'   # 可选：Gitee访问令牌（请替换为你的实际令牌）
    
    # 检查文件是否存在
    if not Path(excel_file).exists():
        print(f"错误: 找不到文件 '{excel_file}'")
        print("请确保Excel文件在当前目录下，并且文件名正确")
        return
    
    # 读取Excel文件中的仓库信息
    repos = read_excel_file(excel_file)
    
    if not repos:
        print("未找到有效的仓库链接")
        return
    
    # 存储所有仓库的提交数据
    all_commits_data = []
    
    # 遍历每个仓库，获取提交历史，去重处理
    processed_students = set()  # 用于避免重复处理
    
    # 先去重，确保每个学生只处理一次
    unique_repos = []
    for repo in repos:
        student_key = (repo['name'], repo['student_id'])
        if student_key not in processed_students:
            processed_students.add(student_key)
            unique_repos.append(repo)
    
    print(f"总共 {len(unique_repos)} 个唯一学生仓库需要处理")
    
    for i, repo in enumerate(unique_repos):
        print(f"正在处理学生: {repo['name']} 的仓库 ({i+1}/{len(unique_repos)})")
        
        if repo['platform'] == 'github':
            commits = get_github_commits(repo['username'], repo['repo'], github_token)
        elif repo['platform'] == 'gitee':
            commits = get_gitee_commits(repo['username'], repo['repo'], gitee_token)
        else:
            commits = []
        
        # 添加延迟以避免API限制
        if i < len(unique_repos) - 1:  # 最后一个不需要延迟
            time.sleep(2)  # 增加到2秒延迟
        
        all_commits_data.append({
            'name': repo['name'],
            'student_id': repo['student_id'],
            'class': repo['class'],
            'original_url': repo['original_url'],  # 使用原始链接
            'url': f"https://github.com/{repo['username']}/{repo['repo']}" if repo['platform'] == 'github' 
                  else f"https://gitee.com/{repo['username']}/{repo['repo']}",
            'commits': commits
        })
    
    # 写入报告文件
    write_commit_report(all_commits_data, output_file)
    print(f"提交报告已生成: {output_file}")
    print("\n提示:")
    print("1. 如果仍然遇到API访问受限问题，请获取并配置访问令牌")
    print("2. '未找到提交记录'表示仓库存在但没有提交，或者由于权限问题无法访问提交历史")
    print("3. 可以尝试增加延迟时间（修改time.sleep()中的数值）")

if __name__ == "__main__":
    main()